﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
/******************************************************************************************************************
* 
* 
* 说　明： DbHelper(版本：Version1.0.0)
* 作　者：李朝强
* 日　期：2015/05/19
* 修　改：
* 参　考：http://my.oschina.net/lichaoqiang/
* 备　注：暂无...
* 
* 
* ***************************************************************************************************************/
namespace SQLMAPApp
{

    /// <summary>
    /// <![CDATA[SQL-MAP DbHelper]]>
    /// </summary>
    public abstract class DbHelper
    {
        /// <summary>
        /// <![CDATA[数据库练级字符串]]>
        /// </summary>
        private static readonly SQLMap.Core.Provider.SqlMapConfigProvider sqlMapConfigProvider = null;

        /// <summary>
        /// 
        /// </summary>
        static DbHelper()
        {
            sqlMapConfigProvider = new SQLMap.Core.Provider.SqlMapConfigProvider();
        }

        /// <summary>
        /// 连接字符串
        /// </summary>
        public static string DbConnectionString
        {
            get
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString;
            }
        }

        /// <summary>
        /// <![CDATA[Command对象]]>
        /// </summary>
        /// <param name="sqlMapCommand"></param>
        /// <param name="Connection"></param>
        /// <returns></returns>
        public static IDbCommand PreDbCommand(SQLMap.Core.Command sqlMapCommand, IDbConnection connection, object obj = null)
        {
            IDbCommand command = sqlMapCommand.Build(connection, obj);
            return command;
        }

        /// <summary>
        /// <![CDATA[创建并打开数据库连接]]>
        /// </summary>
        /// <returns></returns>
        public static IDbConnection CreateAndOpenDbConnection()
        {
            IDbConnection connection = new SqlConnection(DbConnectionString);
            if (connection.State != ConnectionState.Open) { connection.Open(); }
            return connection;
        }

        /// <summary>
        /// <![CDATA[执行SQL,返回受影响的记录数]]>
        /// </summary>
        /// <param name="sqlMapCommand">sqlMapCommand</param>
        /// <param name="parameters"><![CDATA[值value]]></param>
        /// <returns></returns>
        public static int ExcuteNonQuery(SQLMap.Core.Command sqlMapCommand, object parameters)
        {
            Int32 iRowEffect = 0;
            using (IDbConnection connection = CreateAndOpenDbConnection())
            {
                IDbCommand command = PreDbCommand(sqlMapCommand, connection, parameters);
                iRowEffect = command.ExecuteNonQuery();
            }
            return iRowEffect;
        }

        /// <summary>
        /// <![CDATA[执行SQL,返回受影响的记录数]]>
        /// </summary>
        /// <param name="sqlId"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int ExcuteNonQuery(string sqlId, object parameters = null)
        {
            SQLMap.Core.Command sqlMapCommand = sqlMapConfigProvider.GetCommandFromSqlMapById(sqlId: sqlId);
            return ExcuteNonQuery(sqlMapCommand, parameters);
        }

        /// <summary>
        /// <![CDATA[执行SQL，返回IDataReader对象]]>
        /// </summary>
        /// <param name="sqlMapCommand"></param>
        /// <returns></returns>
        public static IDataReader ExcuteDataReader(SQLMap.Core.Command sqlMapCommand, object parameters = null)
        {
            IDbConnection connection = CreateAndOpenDbConnection();
            IDbCommand command = PreDbCommand(sqlMapCommand, connection, parameters);
            IDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return dataReader;
        }


        /// <summary>
        ///  <![CDATA[执行SQL，返回IDataReader对象]]>
        /// </summary>
        /// <param name="sqlId"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static IDataReader ExecuteDataReader(string sqlId, object parameters)
        {
            SQLMap.Core.Command sqlMapCommand = sqlMapConfigProvider.GetCommandFromSqlMapById(sqlId: sqlId);
            return ExcuteDataReader(sqlMapCommand, parameters);
        }

        /// <summary>
        /// <![CDATA[执行SQL，返回SQLMap]]>
        /// </summary>
        /// <param name="sqlMapCommand"></param>
        /// <param name="parameters"></param>
        /// <param name="dataSetName"></param>
        /// <returns></returns>
        public static DataSet Query(SQLMap.Core.Command sqlMapCommand, object parameters = null, string dataSetName = "dataset")
        {
            DataSet ds = null;
            using (IDbConnection connection = CreateAndOpenDbConnection())
            {
                IDbCommand command = PreDbCommand(sqlMapCommand, connection, parameters);
                IDbDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                ds = new DataSet(dataSetName);
                adapter.Fill(ds);
            }
            return ds;
        }

        /// <summary>
        ///<![CDATA[执行查询]]>
        /// </summary>
        /// <param name="sqlId"></param>
        /// <param name="parameters"></param>
        /// <param name="dataSetName"></param>
        /// <returns></returns>
        public static DataSet Query(string sqlId, object parameters = null, string dataSetName = "dataset")
        {
            SQLMap.Core.Command sqlMapCommand = sqlMapConfigProvider.GetCommandFromSqlMapById(sqlId: sqlId);
            return Query(sqlMapCommand: sqlMapCommand, parameters: parameters, dataSetName: dataSetName);
        }

        /// <summary>
        /// <![CDATA[返回首行首列]]>
        /// </summary>
        /// <param name="sqlMapCommand"></param>
        /// <returns></returns>
        public static object ExecuteScalar(SQLMap.Core.Command sqlMapCommand, object parameters = null)
        {
            object value = null;
            using (IDbConnection connection = CreateAndOpenDbConnection())
            {
                IDbCommand command = PreDbCommand(sqlMapCommand, connection, parameters);
                value = command.ExecuteScalar();
            }
            return value;
        }

        /// <summary>
        /// <![CDATA[返回首行首列]]>
        /// </summary>
        /// <param name="sqlId"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqlId, object parameters = null)
        {
            SQLMap.Core.Command sqlMapCommand = sqlMapConfigProvider.GetCommandFromSqlMapById(sqlId: sqlId);
            return Query(sqlMapCommand: sqlMapCommand, parameters: parameters);
        }

    }
}
